﻿using SocanCode.Model;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Stopwatch = System.Diagnostics.Stopwatch;
namespace Fabrics
{
    class OracleSchema : ISchema
    {
        DBUtility.DbHelper helper = null;
        private
        /// <summary>
        /// Oracle的主版本号
        /// </summary>
        int? majorVersion = null;
        /// <summary>
        /// Oracle的次版本号
        /// </summary>
        int? minorVersion = null;
        Dictionary<string, long> ticks = new Dictionary<string, long>();
        Dictionary<string, long> ms = new Dictionary<string, long>();

        public SocanCode.Model.Database GetSchema(string connectionString, SocanCode.Model.DatabaseTypes type)
        {
            SocanCode.Model.Database db = new SocanCode.Model.Database();
            db.ConnString = connectionString;
            db.Type = type;
            helper = DBUtility.DbHelper.Create("Oracle");
            helper.ConnectionString = connectionString;
            GetVersion(db);
            GetTablesAndViewsAndProcedures(db);
            return db;
        }

        private void GetVersion(SocanCode.Model.Database db)
        {
            DataSet ds = helper.ExecuteDataset(CommandType.Text, "SELECT version FROM V$INSTANCE", null);
            db.Version = SchemaHelper.GetString(ds.Tables[0].Rows[0][0]);
            if (!string.IsNullOrWhiteSpace(db.Version))
            {
                string[] versionSegments = db.Version.Split(new[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                if (versionSegments.Length > 0)
                {
                    int tempMajorVersion;
                    if (int.TryParse(versionSegments[0], out tempMajorVersion))
                    {
                        majorVersion = tempMajorVersion;
                    }
                }
                if (versionSegments.Length > 1)
                {
                    int tempMinorVersion;
                    if (int.TryParse(versionSegments[1], out tempMinorVersion))
                    {
                        minorVersion = tempMinorVersion;
                    }
                }
            }
        }

        private void GetTablesAndViewsAndProcedures(SocanCode.Model.Database db)
        {
            using (OracleConnection conn = helper.CreateConnection() as OracleConnection)
            {
                conn.Open();
                DataSet ds;
                const string TABLE_NAMES = "table_names";
                const string VIEW_NAMES = "view_names";
                const string COLUMN_DETAILS = "column_details";
                const string PRIMARY_KEYS = "primary_keys";
                const string FOREIGN_KEYS = "foreign_keys";
                const string TABLE_COMMENTS = "table_comments";
                const string COLUMN_COMMENTS = "column_comments";
                const string IDENTITIES = "identites";
                const string PROCEDURES = "procedures";
                var parameterNames = new List<string> { TABLE_NAMES, VIEW_NAMES, COLUMN_DETAILS, PRIMARY_KEYS, FOREIGN_KEYS, TABLE_COMMENTS, COLUMN_COMMENTS, PROCEDURES };
                if (majorVersion.HasValue && majorVersion.Value >= 12)
                {
                    parameterNames.Add(IDENTITIES);
                }
                StringBuilder sql = new StringBuilder();

                sql.Append("begin ");
                sql.AppendFormat("open :{0} for select table_name from user_tables order by table_name; ", TABLE_NAMES); //获取所有表的名称
                sql.AppendFormat("open :{0} for select view_name from user_views order by view_name; ", VIEW_NAMES); //获取所有视图的名称
                sql.AppendFormat("open :{0} for select * from user_tab_columns order by table_name; ", COLUMN_DETAILS); //获取所有列的信息
                sql.AppendFormat("open :{0} for select col.table_name, col.column_name, col.position from user_constraints con,  user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and con.status = 'ENABLED' order by col.table_name, col.position; ", PRIMARY_KEYS); //获取所有主键的信息
                sql.AppendFormat(@"
open :{0} for select child_con.constraint_name as constraint_name, child_columns.table_name as table_name, child_con.delete_rule as delete_rule, child_columns.column_name as column_name, child_columns.position as position, parent_columns.table_name as referenced_table_name, parent_columns.column_name as referenced_column_name
from user_constraints child_con
    inner join user_cons_columns child_columns on child_con.constraint_name = child_columns.constraint_name
    inner join user_constraints parent_con on child_con.r_constraint_name = parent_con.constraint_name
    inner join user_cons_columns parent_columns on parent_con.constraint_name = parent_columns.constraint_name and child_columns.position = parent_columns.position
where child_con.constraint_type = 'R' and parent_con.constraint_type = 'P' and child_con.status = 'ENABLED' and parent_con.status = 'ENABLED'
order by child_con.table_name, parent_con.table_name, child_columns.position;", FOREIGN_KEYS);//获取所有外键信息
                sql.AppendFormat("open :{0} for select table_name, comments from user_tab_comments  order by table_name; ", TABLE_COMMENTS); //获取所有表的描述
                sql.AppendFormat("open :{0} for select table_name, column_name, comments from user_col_comments order by table_name; ", COLUMN_COMMENTS); //获取所有列的描述
                sql.AppendFormat("open :{0} for select * from user_procedures where procedure_name is null; ", PROCEDURES); //获取所有存储过程
                if (majorVersion.HasValue && majorVersion.Value >= 12)
                {
                    //从Oracle 12.1开始支持标识列（IDENTITY）
                    sql.AppendFormat("open :{0} for select table_name, column_name from user_tab_identity_cols order by table_name; ", IDENTITIES); //获取所有表中的标识列
                }
                sql.Append("end;");

                OracleCommand cmd = new OracleCommand(sql.ToString(), conn);
                foreach (var parameterName in parameterNames)
                {
                    OracleParameter p = cmd.Parameters.Add(":" + parameterName, OracleDbType.RefCursor);
                    p.Direction = ParameterDirection.Output;
                }

                cmd.ExecuteNonQuery();

                ds = new DataSet("Database Information");
                foreach (OracleParameter oracleParameter in cmd.Parameters.Cast<OracleParameter>().Where(a => a.OracleDbType == OracleDbType.RefCursor && a.Direction == ParameterDirection.Output))
                {
                    OracleDataReader dataReader = ((OracleRefCursor)oracleParameter.Value).GetDataReader();
                    var dataTable = ds.Tables.Add(oracleParameter.ParameterName.Substring(1));
                    dataTable.Load(dataReader);
                }
                const string TABLE_TYPE = "TABLE";
                const string VIEW_TYPE = "VIEW";
                //获取所有表的名称
                List<string> tableNames = ds.Tables[TABLE_NAMES].AsEnumerable().Select(a => a.Field<string>("TABLE_NAME")).ToList();
                //获取所有视图的名称
                List<string> viewNames = ds.Tables[VIEW_NAMES].AsEnumerable().Select(a => a.Field<string>("VIEW_NAME")).ToList();

                var tableOrViewNames = (from tableName in tableNames
                                        select new { Type = TABLE_TYPE, Name = tableName }).Concat
                                             (from viewName in viewNames
                                              select new { Type = VIEW_TYPE, Name = viewName }).ToList();

                //获取所有存储过程
                foreach (DataRow r in ds.Tables[PROCEDURES].Rows)
                {
                    db.StoreProcedures.Add(SchemaHelper.GetString(r[0]));
                }

                //获取所有表的列的信息
                var allTableColumns = ds.Tables[COLUMN_DETAILS].AsEnumerable().GroupBy(a => a.Field<string>("TABLE_NAME")).ToDictionary(a => a.Key, a => a.ToList());
                //获取所有表的主键的信息
                var allPrimaryKeys = ds.Tables[PRIMARY_KEYS].AsEnumerable().GroupBy(a => a.Field<string>("TABLE_NAME")).ToDictionary(a => a.Key, a => a.ToList());
                //获取所有表的外键的信息
                var allForeignKeys = ds.Tables[FOREIGN_KEYS].AsEnumerable().GroupBy(a => a.Field<string>("TABLE_NAME")).ToDictionary(a => a.Key, a => a.ToList());
                //获取所有表的描述
                var allTableComments = ds.Tables[TABLE_COMMENTS].AsEnumerable().ToDictionary(a => a.Field<string>("TABLE_NAME"), a => a.Field<string>("COMMENTS"));
                //获取所有表的列描述
                var allColumnComments = ds.Tables[COLUMN_COMMENTS].AsEnumerable().GroupBy(a => a.Field<string>("TABLE_NAME")).ToDictionary(a => a.Key, a => a.ToList());
                Dictionary<string, List<DataRow>> allIdentityColumns = null;
                if (majorVersion.HasValue && majorVersion.Value >= 12)
                {
                    //从Oracle 12.1开始支持标识列（IDENTITY）
                    //获取所有表中的标识列
                    allIdentityColumns = ds.Tables[IDENTITIES].AsEnumerable().GroupBy(a => a.Field<string>("TABLE_NAME")).ToDictionary(a => a.Key, a => a.ToList());
                }

                Dictionary<string, Table> tableMap = new Dictionary<string, Table>();

                foreach (var tableOrViewName in tableOrViewNames)
                {
                    SocanCode.Model.Table table = new SocanCode.Model.Table();
                    table.Name = tableOrViewName.Name;
                    List<DataRow> columns = allTableColumns[table.Name];
                    List<DataRow> primaryKeys;
                    allPrimaryKeys.TryGetValue(table.Name, out primaryKeys);
                    string tableDescn;
                    if (allTableComments.TryGetValue(table.Name, out tableDescn))
                    {
                        table.Descn = tableDescn;
                    }

                    List<DataRow> columnDescns;
                    allColumnComments.TryGetValue(table.Name, out columnDescns);
                    List<DataRow> identityColumns = null;
                    if (allIdentityColumns != null)
                    {
                        allIdentityColumns.TryGetValue(table.Name, out identityColumns);
                    }
                    GetColumns(table, columns, primaryKeys, columnDescns, identityColumns);
                    table.Fields.Sort();
                    switch (tableOrViewName.Type)
                    {
                        case TABLE_TYPE:
                            db.AddTable(table);
                            tableMap[table.Name] = table;
                            break;
                        case VIEW_TYPE:
                            db.AddView(table);
                            break;
                        default:
                            throw new Exception(string.Format("无效的表类型{0}，表名：{1}。表类型必须是TABLE或VIEW。", tableOrViewName.Type, table.Name));
                    }
                }

                //写入外键信息
                foreach (var table in tableMap.Values)
                {
                    table.ForeignKeys = new List<ForeignKey>();
                    List<DataRow> foreignKeys;
                    if (allForeignKeys.TryGetValue(table.Name, out foreignKeys) && foreignKeys != null && foreignKeys.Count > 0)
                    {
                        foreach (var foreignKeyGroup in foreignKeys.GroupBy(a => SchemaHelper.GetString(a["REFERENCED_TABLE_NAME"])))
                        {
                            var foreignKey = new ForeignKey();
                            foreignKey.SlaveTable = table;
                            foreignKey.MasterTable = tableMap[foreignKeyGroup.Key];
                            foreignKey.ForeignKeyFields = new List<ForeignKeyColumn>();
                            int i = 0;
                            foreach (DataRow foreignKeyItem in foreignKeyGroup.OrderBy(a=> SchemaHelper.GetInt(a["POSITION"])))
                            {
                                var constraintName = SchemaHelper.GetString(foreignKeyItem["CONSTRAINT_NAME"]);
                                var foreignKeyColumn = new ForeignKeyColumn();
                                var local_column_name = SchemaHelper.GetString(foreignKeyItem["COLUMN_NAME"]);
                                var foreignKeyLocalField = table.Fields.First(a => a.Name == local_column_name);
                                foreignKeyLocalField.IsForeignKey = true;
                                foreignKeyColumn.LocalField = foreignKeyLocalField;
                                var referenced_column_name = SchemaHelper.GetString(foreignKeyItem["REFERENCED_COLUMN_NAME"]);
                                foreignKeyColumn.ReferencedField = foreignKey.MasterTable.Fields.First(a => a.Name == referenced_column_name);
                                var position = SchemaHelper.GetInt(foreignKeyItem["POSITION"]);
                                foreignKeyColumn.Position = position;
                                foreignKey.ForeignKeyFields.Add(foreignKeyColumn);
                                
                                if (i == 0)
                                {
                                    var deleteRule = SchemaHelper.GetString(foreignKeyItem["DELETE_RULE"]);
                                    switch (deleteRule)
                                    {
                                        case "CASCADE":
                                            foreignKey.DeleteRule = ForeignKeyDeleteRule.Cascade;
                                            break;
                                        case "NO ACTION":
                                            foreignKey.DeleteRule = ForeignKeyDeleteRule.NoAction;
                                            break;
                                        case "SET NULL":
                                            foreignKey.DeleteRule = ForeignKeyDeleteRule.SetNull;
                                            break;
                                        default:
                                            throw new Exception(string.Format("无效的DELETE_RULE，外键名：{0}", constraintName));
                                    }
                                }
                                i++;
                            }
                            table.ForeignKeys.Add(foreignKey);
                        }
                    }
                }

            }
        }

        private void GetColumns(SocanCode.Model.Table table, List<DataRow> columns, List<DataRow> primaryKeys, List<DataRow> columnDescns, List<DataRow> identityColumns)
        {
            Dictionary<string, SocanCode.Model.Field> fieldMap = new Dictionary<string, SocanCode.Model.Field>(StringComparer.InvariantCultureIgnoreCase);

            //所有字段
            foreach (DataRow r in columns)
            {
                SocanCode.Model.Field field = new SocanCode.Model.Field();
                field.AllowNull = SchemaHelper.GetString(r["NULLABLE"]).Equals("Y", StringComparison.CurrentCultureIgnoreCase);
                field.DefaultValue = r["DATA_DEFAULT"].ToString();
                field.FieldType = SchemaHelper.GetString(r["DATA_TYPE"]);
                field.Length = SchemaHelper.GetInt(r["DATA_LENGTH"]);
                field.Name = SchemaHelper.GetString(r["COLUMN_NAME"]);
                field.Pos = SchemaHelper.GetInt(r["COLUMN_ID"]);
                field.Precision = SchemaHelper.GetNullableInt(r["DATA_PRECISION"]);
                field.Scale = SchemaHelper.GetNullableInt(r["DATA_SCALE"]);
                field.Size = (int)field.Length;
                table.AddField(field);
                fieldMap[field.Name] = field;
            }

            //主键
            if (primaryKeys != null && primaryKeys.Count > 0)
            {
                foreach (DataRow r in primaryKeys.OrderBy(a => SchemaHelper.GetInt(a["POSITION"])))
                {
                    string key = SchemaHelper.GetString(r["COLUMN_NAME"]);
                    SocanCode.Model.Field field;
                    if (fieldMap.TryGetValue(key, out field))
                    {
                        field.IsKey = true;
                        table.PrimaryKey.Add(field);
                    }
                }
            }

            //列描述
            if (columnDescns != null && columnDescns.Count > 0)
            {
                foreach (DataRow r in columnDescns)
                {
                    var columnName = SchemaHelper.GetString(r["COLUMN_NAME"]);
                    SocanCode.Model.Field field;
                    if (fieldMap.TryGetValue(columnName, out field))
                    {
                        field.Descn = SchemaHelper.GetString(r["COMMENTS"]);
                    }
                }
            }

            //标识列
            if (identityColumns != null && identityColumns.Count > 0)
            {
                foreach (DataRow r in identityColumns)
                {
                    string key = SchemaHelper.GetString(r[0]);
                    SocanCode.Model.Field field;
                    if (fieldMap.TryGetValue(key, out field))
                    {
                        field.IsId = true;
                        break;
                    }
                }
            }
        }
    }
}
